package recycle.repository;

import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.json.JsonObject;
import io.vertx.mysqlclient.MySQLClient;
import io.vertx.mysqlclient.MySQLPool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple;
import recycle.pojo.Appointment;

import java.util.ArrayList;
import java.util.List;

public class AppointmentRepository {
    private final MySQLPool client;

    public AppointmentRepository(MySQLPool client) {
        this.client = client;
    }

    public MySQLPool getClient() {
        return client;
    }

    private Appointment rowToAppointment(Row row) {
        Appointment appointment = new Appointment();
        appointment.setId(row.getLong("id"));
        appointment.setName(row.getString("name"));
        appointment.setUserId(row.getLong("user_id"));
        appointment.setTime(row.getString("time"));
        appointment.setCategoryId(row.getLong("category_id"));
        appointment.setStationId(row.getLong("station_id"));
        appointment.setWeight(row.getInteger("weight"));
        appointment.setStatus(row.getInteger("status"));
        appointment.setFeedback(row.getString("feedback"));
        appointment.setReply(row.getString("reply"));
        return appointment;
    }

    public Future<Long> insert(Appointment appointment) {
        return client.preparedQuery("INSERT INTO appointment (name,user_id, category_id, weight, time) VALUES (?, ?, ?, ?, ?) ")
                .execute(Tuple.of(appointment.getName(),appointment.getUserId(), appointment.getCategoryId(), appointment.getWeight(), appointment.getTime()))
                .map(rows -> rows.property(io.vertx.mysqlclient.MySQLClient.LAST_INSERTED_ID));
    }

    public Future<Long> createAppointmentWithTransaction(Appointment appointment) {
        return client.withTransaction(conn ->
                conn.preparedQuery("SELECT id FROM station ORDER BY storage ASC LIMIT 1 FOR UPDATE")
                        .execute()
                        .compose(rows -> {
                            if (rows.size() == 0) {
                                return Future.failedFuture("No available station found");
                            }
                            Long stationId = rows.iterator().next().getLong("id");

                            // 直接插入预约记录，不更新station
                            return conn.preparedQuery(
                                            "INSERT INTO appointment (name, user_id, category_id, weight, time, station_id) " +
                                                    "VALUES (?, ?, ?, ?, ?, ?)")
                                    .execute(Tuple.of(
                                            appointment.getName(),
                                            appointment.getUserId(),
                                            appointment.getCategoryId(),
                                            appointment.getWeight(),
                                            appointment.getTime(),
                                            stationId
                                    ))
                                    .map(rows2 -> rows2.property(MySQLClient.LAST_INSERTED_ID));
                        })
        );
    }

    public Future<JsonObject> selectList(Appointment appointment, Integer pageNum, Integer pageSize) {
        // 参数校验和默认值设置
        if (pageNum == null || pageNum < 1) pageNum = 1;
        if (pageSize == null || pageSize < 1) pageSize = 10;

        // 计算偏移量
        int offset = (pageNum - 1) * pageSize;

        // 构建基础SQL
        StringBuilder sql = new StringBuilder("SELECT * FROM appointment WHERE 1=1");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) AS total FROM appointment WHERE 1=1");

        // 使用List收集参数更灵活
        List<Object> queryParams = new ArrayList<>();
        List<Object> countParams = new ArrayList<>();

        // 添加条件
        if (appointment.getId() != null && appointment.getId() != 0) {
            sql.append(" AND id = ?");
            countSql.append(" AND id = ?");
            queryParams.add(appointment.getId());
            countParams.add(appointment.getId());
        }

        if (appointment.getUserId() != null && appointment.getUserId() != 0) {
            sql.append(" AND user_id = ?");
            countSql.append(" AND user_id = ?");
            queryParams.add(appointment.getUserId());
            countParams.add(appointment.getUserId());
        }

        if (appointment.getCategoryId() != null && appointment.getCategoryId() != 0) {
            sql.append(" AND category_id = ?");
            countSql.append(" AND category_id = ?");
            queryParams.add(appointment.getCategoryId());
            countParams.add(appointment.getCategoryId());
        }

        if (appointment.getStationId() != null && appointment.getStationId() != 0) {
            sql.append(" AND station_id = ?");
            countSql.append(" AND station_id = ?");
            queryParams.add(appointment.getStationId());
            countParams.add(appointment.getStationId());
        }

        if (appointment.getStatus() != null) {
            sql.append(" AND status = ?");
            countSql.append(" AND status = ?");
            queryParams.add(appointment.getStatus());
            countParams.add(appointment.getStatus());
        }
        sql.append(" ORDER BY time DESC");


        // 为查询SQL添加分页参数
        sql.append(" LIMIT ? OFFSET ?");
        queryParams.add(pageSize);
        queryParams.add(offset);

        // 转换为Tuple
        Tuple queryTuple = Tuple.from(queryParams.toArray());
        Tuple countTuple = Tuple.from(countParams.toArray());

        // 执行查询和计数
        Future<RowSet<Row>> queryFuture = client.preparedQuery(sql.toString())
                .execute(queryTuple);

        Future<RowSet<Row>> countFuture = client.preparedQuery(countSql.toString())
                .execute(countTuple);

        // 合并结果
        return CompositeFuture.all(queryFuture, countFuture)
                .compose(composite -> {
                    RowSet<Row> rows = composite.resultAt(0);
                    RowSet<Row> countRows = composite.resultAt(1);

                    List<Appointment> appointmentList = new ArrayList<>();
                    for (Row row : rows) {
                        appointmentList.add(rowToAppointment(row));
                    }

                    long total = countRows.iterator().next().getLong("total");
                    // int totalPages = (int) Math.ceil((double) total / pageSize);

                    // 构建返回结果
                    return Future.succeededFuture(new JsonObject()
                                    .put("records", appointmentList)
                                    .put("total", total)
                            // .put("pageNum", pageNum)
                            // .put("pageSize", pageSize)
                            // .put("totalPages", totalPages)
                    );
                });
    }


    public Future<Appointment> selectById(Long id) {
        return client.preparedQuery("SELECT * FROM appointment WHERE id = ?")
                .execute(Tuple.of(id))
                .map(rows -> {
                    if (rows.size() == 0) {
                        return null;
                    }
                    Row row = rows.iterator().next();
                    return rowToAppointment(row);
                });
    }

    // public Future<Void> updateById(Appointment appointment) {
    //     return client.preparedQuery(" UPDATE appointment SET name = ? WHERE id = ? ")
    //             .execute(Tuple.of(appointment.getName(),  appointment.getId()))
    //             .mapEmpty();
    // }

    public Future<Void> updateById(Appointment appointment) {
        StringBuilder sql = new StringBuilder("UPDATE appointment SET ");
        List<Object> params = new ArrayList<>();
        boolean hasUpdateFields = false;

        // 动态构建 SET 子句
        if (appointment.getStationId() != null && appointment.getStationId() != 0) {
            sql.append("station_id = ?, ");
            params.add(appointment.getStationId());
            hasUpdateFields = true;
        }

        if (appointment.getStatus() != null) {
            sql.append("status = ?, ");
            params.add(appointment.getStatus());
            hasUpdateFields = true;
        }

        if (appointment.getFeedback() != null && !appointment.getFeedback().isEmpty()) {
            sql.append("feedback = ?, ");
            params.add(appointment.getFeedback());
            hasUpdateFields = true;
        }

        if (appointment.getReply() != null && !appointment.getFeedback().isEmpty()) {
            sql.append("reply = ?, ");
            params.add(appointment.getReply());
            hasUpdateFields = true;
        }

        // 如果没有需要更新的字段，直接返回成功
        if (!hasUpdateFields) {
            return Future.succeededFuture();
        }

        // 移除最后一个逗号和空格
        sql.delete(sql.length() - 2, sql.length());

        // 添加 WHERE 条件
        sql.append(" WHERE id = ?");
        params.add(appointment.getId());

        // 执行更新
        return client.preparedQuery(sql.toString())
                .execute(Tuple.from(params.toArray()))
                .mapEmpty();
    }

    public Future<Void> deleteById(Long id) {
        return client.preparedQuery("DELETE FROM appointment WHERE id = ?")
                .execute(Tuple.of(id))
                .mapEmpty();
    }

    public Future<List<Appointment>> selectListByUserId(Long userId) {
        return client.preparedQuery("SELECT * FROM appointment where user_id = ? order by time DESC")
                .execute(Tuple.of(userId))
                .map(rows -> {
                    List<Appointment> list = new ArrayList<>(rows.size());
                    for (Row row : rows) {
                        list.add(rowToAppointment(row));
                    }
                    return list;
                });
    }
}